package valuecomusa.fijibus.eticket.db.shorthaul;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import valuecomusa.fijibus.eticket.Utilities;
import valuecomusa.fijibus.eticket.dto.StagesOfRouteValue;
import valuecomusa.fijibus.eticket.dto.SyncStatusValue;
import valuecomusa.fijibus.eticket.dto.TransactionDetailValue;
import valuecomusa.fijibus.eticket.ws.*;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class BusinessDBHandler extends SQLiteOpenHelper
{
	private static final String synchronizedLock = "LOCK";
	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;
    private final String TAG = "DBHandler-Short";
	// Database Name
	private static String DATABASE_NAME = "shorthaul_localstore.db";

	public BusinessDBHandler(Context context)
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db)
	{
		Log.d(TAG, "Setup new database...");
		try
		{
			String CREATE_MEMBER_TABLE = "CREATE TABLE Members("
					+ "isn integer primary key,"
					+ "username text not null unique,"
					+ "firstname text,"
                    + "lastname text,"
                    + "passengertype text,"
					+ "cardnumber text not null unique collate nocase,"
                    + "tagserial text collate nocase,"
                    + "picture text,"
					+ "currentbalance real NOT NULL)";

            String CREATE_CONDUCTOR_TABLE = "CREATE TABLE Conductors(" +
                    "isn integer primary key," +
                    "username text not null unique collate nocase," +
                    "fullname text," +
                    "pwd text" +
                    ")";

            String CREATE_BUS_TABLE = "CREATE TABLE [Bus] ("
                + "[isn] INTEGER NOT NULL,"
                + "[busnumber] TEXT NOT NULL,"
                + "[capacity] INTEGER"
                + ")";
            String CREATE_ROUTE_TABLE = "CREATE TABLE [Routes] ("
                + "[isn] INTEGER NOT NULL,"
                + "[name] TEXT NOT NULL,"
                + "[type] INTEGER NOT NULL"
                + ")";
            String CREATE_ROUTEBUS_TABLE = "CREATE TABLE [Route_Bus] ("
                    + "[isn] INTEGER NOT NULL,"
                    + "[routeisn] INTEGER NOT NULL,"
                    + "[busisn] INTEGER"
                    + ");";
            String CREATE_STAGE_TABLE = "CREATE TABLE [Stage] ("
                    + "[isn] INTEGER NOT NULL,"
                    + "[routeisn] INTEGER NOT NULL,"
                    + "[stageorder] INTEGER NOT NULL"
                    + ");";
            String CREATE_BUSSTATION_TABLE = "CREATE TABLE [BusStation] ("
                    + "[isn] INTEGER NOT NULL,"
                    + "[name] TEXT NOT NULL,"
                    + "[longitude] REAL,"
                    + "[latitude] REAL"
                    + ");";
            String CREATE_BUSSTOP_TABLE = "CREATE TABLE [BusStop] ("
                    + "[isn] INTEGER NOT NULL,"
                    + "[stageisn] INTEGER NOT NULL,"
                    + "[busstationisn] INTEGER NOT NULL"
                    + ");";

            String CREATE_STAGE_VIEW = "CREATE VIEW vw_Stage" +
                    " as " +
                    " select a.routeisn, a.isn stageisn, a.stageorder, " +
                    " c.isn busstationisn, c.Name as stationname, c.longitude, c.latitude " +
                    " from Stage a, BusStop b, BusStation c" +
                    " where a.ISN=b.StageISN and b.BusStationISN=c.ISN";

            String CREATE_TABLE_JOURNAL = "CREATE TABLE Journal (\n" +
                    "           isn INTEGER primary key autoincrement,\n" +
                    "           timestamp text,\n" +
                    "           journalid int,\n" +
                    "           busisn int,\n" +
                    "           routeisn int,\n" +
                    "           driverisn int,\n" +
                    "           conductorisn int,\n" +
                    "           longitude real,\n" +
                    "           latitude real,\n" +
                    "           deviceid text,\n" +
                    "           closestbusstation text default '',\n" +
                    "           syncisn int);";

            String CREATE_TABLE_EXPRESS_FARE = "CREATE TABLE ExpressFare (\n" +
                    "\tisn int,\n" +
                    "\trouteisn int,\n" +
                    "\tstage1isn int,\n" +
                    "\tstage2isn int,\n" +
                    "\tfare real);";

            String CREATE_TABLE_STAGE_FARE = "CREATE TABLE StageFare (\n" +
                    "\tisn int,\n" +
                    "\trouteisn int,\n" +
                    "\txstage int,\n" +
                    "\tfare real );";

            String CREATE_TABLE_BUS_SEAT = "create table BusSeat (\n" +
                    "\tisn int,\n" +
                    "\tseatnumber text,\n" +
                    "\tseatseq int,\n" +
                    "\tbusisn int,\n" +
                    "\tusedby int);";
            
            String CREATE_TABLE_TRANSACTION = "create table TransactionInfo (\n" +
                    "\tpaymenttype text,\n" +
                    "\tisn integer primary key autoincrement,\n" +
                    "\ttimestamp text,\n" +
                    "\tlongitude real,\n" +
                    "\tlatitude real,\n" +
                    "\ttransactionid text,\n" +
                    "\tmemberisn int,\n" +
                    "\ttype text,\n" +
                    "\tfareamount real,\n" +
                    "\ttopupamount real,\n" +
                    "\tdescription text,\n" +
                    "\tbusisn int,\n" +
                    "\tconductorisn int,\n" +
                    "\tjournalsession int,\n" +
                    "\trouteisn int,\n" +
                    "\torigination text,\n" +
                    "\tdestination text,\n" +
                    "\tcheckoutdate text,\n" +
                    "\tlongitude2 real,\n" +
                    "\tlatitude2 real,\n" +
                    "\tvouchertype text,\n" +
                    "\tvoucheramount real,\n" +
                    "\tfreight real,\n" +
                    "\texessivebag real,\n" +
                    "\tisrefunded int default 0,\n" +
                    "\tsyncisn int default 0);";

            String CREATE_TABLE_TRANSACTION_DETAIL = "create table TransactionDetail(\n" +
                    "\ttransactionisn int,\n" +
                    "\tpassengertype text,\n" +
                    "\tseatnumber text,\n" +
                    "\tfare real);";

            String CREATE_TABLE_DATA_SYNC_STATUS = "create table SyncStatus(\n" +
                    "\tisn int,\n" +
                    "\tstatus text,\n" +
                    "\tlastupdate text,\n" +
                    "\tnextsync text);";

            String CREATE_TABLE_RUNTIME_VALUES_STATUS = "create table RuntimeValues(\n" +
                    "\tname text,\n" +
                    "\tvalue text);";

            String CREATE_TABLE_BUSS_ASSIGNMENT = "create table BusAssignment(\n" +
                    "\tisn int,\n" +
                    "\tbusisn int,\n" +
                    "\tstaffisn int\n" +
                    ");";

            String CREATE_TABLE_PASSENGER_TYPE = "create table PassengerType(\n" +
                    "\tisn int,\n" +
                    "\ttype text,\n" +
                    "\tdiscount real);";

            //---
            db.execSQL(CREATE_MEMBER_TABLE);
            db.execSQL(CREATE_CONDUCTOR_TABLE);
            db.execSQL(CREATE_BUS_TABLE);
            db.execSQL(CREATE_ROUTE_TABLE);
            db.execSQL(CREATE_ROUTEBUS_TABLE);
            db.execSQL(CREATE_STAGE_TABLE);
            db.execSQL(CREATE_BUSSTATION_TABLE);
            db.execSQL(CREATE_BUSSTOP_TABLE);
            db.execSQL(CREATE_STAGE_VIEW);
            db.execSQL(CREATE_TABLE_JOURNAL);
            db.execSQL(CREATE_TABLE_EXPRESS_FARE);
            db.execSQL(CREATE_TABLE_STAGE_FARE);
            db.execSQL(CREATE_TABLE_BUS_SEAT);
            db.execSQL(CREATE_TABLE_TRANSACTION);
            db.execSQL(CREATE_TABLE_TRANSACTION_DETAIL);
            db.execSQL(CREATE_TABLE_DATA_SYNC_STATUS);
            db.execSQL(CREATE_TABLE_RUNTIME_VALUES_STATUS);
            db.execSQL(CREATE_TABLE_BUSS_ASSIGNMENT);
            db.execSQL(CREATE_TABLE_PASSENGER_TYPE);

		}
		catch (Exception ex)
		{
			Log.d(TAG, ex.getMessage());
		}
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	{
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS Member");
        db.execSQL("DROP TABLE IF EXISTS Conductor");
        db.execSQL("DROP TABLE IF EXISTS Bus");
        db.execSQL("DROP TABLE IF EXISTS Route");
        db.execSQL("DROP TABLE IF EXISTS Bus_Route");
        db.execSQL("DROP TABLE IF EXISTS Stage");
        db.execSQL("DROP TABLE IF EXISTS BusStation");
        db.execSQL("DROP TABLE IF EXISTS Bus_Stop");
        db.execSQL("DROP VIEW IF EXISTS vw_Stage");
        db.execSQL("DROP TABLE IF EXISTS Joural");
        db.execSQL("DROP TABLE IF EXISTS ExpressFare");
        db.execSQL("DROP TABLE IF EXISTS StageFare");
        db.execSQL("DROP TABLE IF EXISTS BusSeat");
        db.execSQL("DROP TABLE IF EXISTS TransactionInfo");
        db.execSQL("DROP TABLE IF EXISTS TransactionDetail");
        db.execSQL("DROP TABLE IF EXISTS SyncStatus");
        db.execSQL("DROP TABLE IF EXISTS RuntimeValues");
        db.execSQL("DROP TABLE IF EXISTS BusAssignment");
        db.execSQL("DROP TABLE IF EXISTS PassengerType");

		// Create tables again
		onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// getting customer
	public synchronized MemberValue getMemberByISN(int isn)
	{

        List<MemberValue> results = getTableRows(
                "select * from Members where isn=?",
                new String[]{String.valueOf(isn)},
                MemberValue.class
        );

        if(results != null && results.size() > 0)
            return results.get(0);
        return null;
	}

	// getting customer
	public synchronized MemberValue getMemberByCard(String cardNumber)
	{
        List<MemberValue> results = getTableRows(
                "select * from Members where CardNumber=?",
                new String[]{cardNumber},
                MemberValue.class
        );

        if(results != null && results.size() > 0)
            return results.get(0);
        return null;

	}

    public synchronized MemberValue getMemberByCard(String cardNumber, String tagSerial)
    {
        List<MemberValue> results = getTableRows(
                "select * from Members where CardNumber=? and TagSerial=?",
                new String[]{cardNumber, tagSerial},
                MemberValue.class
        );

        if(results != null && results.size() > 0)
            return results.get(0);

        return null;
    }

	// update balance
	public synchronized int updateMemberBalance(int isn, double newBalance)
	{
		ContentValues values = new ContentValues();
		values.put("CurrentBalance", newBalance);

        return updateTableRow(values, "Members", "ISN", isn);
	}

	// add or update
	public synchronized long addOrUpdateMember(MemberValue mem)
	{
        boolean x = checkDataRowExists("Members", "ISN", mem.ISN);
        long rs;
        if (x)
            //rs = updateConductor(cdt);
            rs = updateTableRow(mem, "Members", "ISN", mem.ISN);
        else
            //rs = addConductor(cdt);
            rs = addTableRow(mem, "Members");

		return rs;
	}

	/*public int updTransSyncISN(int id, int SyncISN)
	{
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put("SyncISN", SyncISN);

		// updating row
		int rs = db.update("Trans", values, "TranISN = ?",
				new String[] { String.valueOf(id) });

		db.close();

		return rs;
	}*/

	// conductor authentication.
	public ConductorValue conductorLogin(String username, String password)
	{
		ConductorValue cdt = null;
		synchronized (synchronizedLock)
		{
			try
			{
				Log.d(TAG, String.format("[conductorLogin] Authorization user: %s, pwd: %s", username, password));
				//--
				SQLiteDatabase db = this.getReadableDatabase();

				Cursor cursor = db.query("Conductors", new String[] { "ISN",
						"UserName", "Pwd", "fullname" }, "username=? and pwd=?",
						new String[] { username, password }, null, null, null, null);

				if (cursor != null)
				{
					if (cursor.moveToFirst())
					{
						cdt = new ConductorValue();
						cdt.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
						cdt.UserName = cursor.getString(cursor.getColumnIndex("username"));
						cdt.FullName = cursor.getString(cursor.getColumnIndex("fullname"));
					}
					cursor.close();
				}
				else
				{
					Log.d(TAG, "[conductorLogin] No data found");
				}

				db.close();
			}
			catch (Exception ex)
			{
				Log.d(TAG, "[conductorLogin] EXCEPTION: " + ex);
			}
		}//end of synchronized

		return cdt;
	}

	// add or upd conductor
	public synchronized long addOrUpdateConductor(ConductorValue cdt)
	{
		boolean x = checkDataRowExists("Conductors", "ISN", cdt.ISN);
		long rs;
		if (x)
            rs = updateTableRow(cdt, "Conductors", "ISN", cdt.ISN);
		else
            rs = addTableRow(cdt, "Conductors");

		return rs;
	}

    public synchronized <T> long addTableRow(T valueObject, String table)
    {
        return addTableRowWithExclusive(valueObject, table, null);
    }

    public <T> long addTableRowWithExclusive(T valueObject, String table, List<String> exclusiveField)
    {
        if(valueObject == null || table == null || table.length() == 0)
            return -1;

		long rs = -1;
		synchronized (synchronizedLock)
		{
			//Log.i(TAG, String.format("[addTableRowWithExclusive] ------%s -------", table));
			//Log.i(TAG, valueObject.getClass().getName());
			//---
			ContentValues values = new ContentValues();
			for(Field field: valueObject.getClass().getFields())
			{
				try
				{
					if(exclusiveField != null && exclusiveField.contains(field.getName()))
						continue;
					if (java.lang.reflect.Modifier.isStatic(field.getModifiers()))
						continue;
					//---
					Type type = field.getGenericType();
					if(type instanceof ParameterizedType)
						continue; //not process yet
					//--
					type = field.getType();

					if(type.equals(String.class))
						values.put(field.getName(), field.get(valueObject).toString());
					else if(type.equals(Long.TYPE))
						values.put(field.getName(), field.getLong(valueObject));
					else if(type.equals(Date.class) && field.get(valueObject) != null)
						values.put(field.getName(), Utilities.DateToString((Date) field.get(valueObject)));
					else if(type.equals(Integer.TYPE))
						values.put(field.getName(), field.getInt(valueObject));
					else if(type.equals(Double.TYPE))
						values.put(field.getName(), field.getDouble(valueObject));
				}
				catch (IllegalAccessException e)
				{
					e.printStackTrace();
					Log.e(TAG, "[addTableRowWithExclusive] IllegalAccessException: " + e.toString());
				}
			}

//			for(String key: values.keySet())
//			{
//				Log.i(TAG, "[" + key + "] = " + values.get(key));
//			}
			//---
			try
			{
				SQLiteDatabase db = this.getWritableDatabase();
				// Inserting Row
				rs = db.insert(table, null, values);
				db.close(); // Closing database connection
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[addTableRowWithExclusive] IllegalAccessException: " + ex.toString());
			}
		}//end of synchronized

        return rs;
    }

    public long addTableRowByContentValues(ContentValues values, String table)
    {
        if(values == null || values.size() == 0 || table == null || table.length() == 0)
            return -1;

		long rs = -1;
        //Log.i(TAG, String.format("[addTableRowByContentValues] ------addTableRow: %s -------", table));
        //---
		synchronized (synchronizedLock)
		{
//			for(String key: values.keySet())
//			{
//				Log.i(TAG, "[" + key + "] = " + values.get(key));
//			}
			//---
			try
			{
				SQLiteDatabase db = this.getWritableDatabase();
				// Inserting Row
				rs = db.insert(table, null, values);
				db.close(); // Closing database connection
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[addTableRowByContentValues] EXCEPTION: " + ex.toString());
			}
		}//end of synchronized

        return rs;
    }

    public <T> int updateTableRow(T valueObject, String table, String keyField, Object keyValue)
    {
        if(valueObject == null
                || table == null || table.length() == 0
                || keyField == null || keyField.length() == 0
                || keyValue == null)
        {
            return -1;
        }

		int rs = -1;

		synchronized (synchronizedLock)
		{
			//Log.i(TAG, String.format("[updateTableRow] ------%s -------", table));
			//Log.i(TAG, "[updateTableRow] " + valueObject.getClass().getName());
			//---
			ContentValues values = new ContentValues();

			for(Field field: valueObject.getClass().getFields())
			{
				try
				{
					Type type = field.getGenericType();
					if(type instanceof ParameterizedType)
						continue; //not process yet
					//--
					type = field.getType();

					if(field.getName().equals(keyField))
						continue;
					//--
					if(type.equals(String.class))
						values.put(field.getName(), field.get(valueObject).toString());
					else if(type.equals(Long.TYPE))
						values.put(field.getName(), field.getLong(valueObject));
					else if(type.equals(Date.class) && field.get(valueObject) != null)
						values.put(field.getName(), Utilities.DateToString((Date)field.get(valueObject)));
					else if(type.equals(Integer.TYPE))
						values.put(field.getName(), field.getInt(valueObject));
					else if(type.equals(Double.TYPE))
						values.put(field.getName(), field.getDouble(valueObject));
				}
				catch (IllegalAccessException e)
				{
					e.printStackTrace();
					Log.e(TAG, "[updateTableRow] IllegalAccessException: " + e.toString());
				}
			}

//			Log.i(TAG, "Key [" + keyField + "] = " + keyValue);
//			for(String key: values.keySet())
//			{
//				Log.i(TAG, "[" + key + "] = " + values.get(key));
//			}
			try
			{
				SQLiteDatabase db = this.getWritableDatabase();
				// updating row
				rs = db.update(table, values, keyField + " = ?",
						new String[] { String.valueOf(keyValue) });

				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[updateTableRow] Exception: " + ex.toString());
			}
		}//end of synchronized

        return rs;
    }

    /*public <T> int updateTableRow(T valueObject, String table, String[] keyField, Object[] keyValue)
    {
        if(valueObject == null
                || table == null || table.length() == 0
                || keyField == null || keyField.length == 0
                || keyValue == null || keyValue.length == 0)
        {
            return -1;
        }

        Log.i(TAG, String.format("------updateTableRow: %s -------", table));
        Log.i(TAG, valueObject.getClass().getName());
        //---
        ContentValues values = new ContentValues();

        for(Field field: valueObject.getClass().getFields())
        {
            try
            {
                Type type = field.getGenericType();
                if(type instanceof ParameterizedType)
                    continue; //not process yet
                //--
                type = field.getType();

                if(field.getName().equals(keyField))
                    continue;
                //--
                if(type.equals(String.class))
                    values.put(field.getName(), field.get(valueObject).toString());
                else if(type.equals(Long.TYPE))
                    values.put(field.getName(), field.getLong(valueObject));
                else if(type.equals(Date.class) && field.get(valueObject) != null)
                    values.put(field.getName(), Utilities.DateToString((Date)field.get(valueObject)));
                else if(type.equals(Integer.TYPE))
                    values.put(field.getName(), field.getInt(valueObject));
                else if(type.equals(Double.TYPE))
                    values.put(field.getName(), field.getDouble(valueObject));
            }
            catch (IllegalAccessException e)
            {
                e.printStackTrace();
            }
        }

        int minField = Math.min(keyField.length, keyValue.length);
        String filterString = "";
        ArrayList<String> filterValue = new ArrayList<String>();
        for(int i = 0; i< minField; i++)
        {
            Log.i(TAG, "Key [" + keyField[i] + "] = " + keyValue[i]);
            //--
            filterString += keyField[i] + "=? ";
            if(i<minField-1)
                filterString += "and ";
            filterValue.add(String.valueOf(keyValue[i]));
        }
        //--log value
        for(String key: values.keySet())
        {
            Log.i(TAG, "[" + key + "] = " + values.get(key));
        }

        SQLiteDatabase db = this.getWritableDatabase();
        // updating row
        int rs = db.update(table, values, filterString, filterValue.toArray(new String[filterValue.size()]));
        //--
        db.close();

        return rs;
    }*/

    public int updateTableRow(ContentValues valueObject, String table, String keyField, Object keyValue)
    {
        if(valueObject == null
                || table == null || table.length() == 0
                || keyField == null || keyField.length() == 0
                || keyValue == null)
        {
            return -1;
        }

		int rs = -1;
		synchronized (synchronizedLock)
		{
			//Log.i(TAG, String.format("[updateTableRow] ------%s -------", table));
			//---
//			Log.i(TAG, "Key [" + keyField + "] = " + keyValue);
//			for(String key: valueObject.keySet())
//			{
//				Log.i(TAG, "[" + key + "] = " + valueObject.get(key));
//			}
			try
			{
				SQLiteDatabase db = this.getWritableDatabase();
				// updating row
				rs = db.update(table, valueObject, keyField + " = ?",
						new String[] { String.valueOf(keyValue) });

				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[updateTableRow] Exception: " + ex.toString());
			}
		}//synchronized

        return rs;
    }

    public boolean checkDataRowExists(String table, String keyField, Object value)
    {
        if(value == null)
            throw new NullPointerException("Value cannot null");
		int count = 0;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = String.format("select * from %s where %s=?", table, keyField);
				SQLiteDatabase db = this.getReadableDatabase();

				Cursor cursor = db.rawQuery(sql, new String[]{value.toString()});

				if (cursor == null)
					return false;

				count = cursor.getCount();

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[checkDataRowExists] Exception: " + ex.toString());
			}
		}//end of synchronized

        return (count > 0);
    }

    public synchronized long addOrUpdateBus(BusValue bus)
    {
        boolean x = checkDataRowExists("Bus", "ISN", bus.ISN);
        long rs;
        if (x)
            rs = updateTableRow(bus, "Bus", "ISN", bus.ISN);
        else
            rs = addTableRow(bus, "Bus");

        return rs;
    }

    public synchronized long addOrUpdateRoute(RouteValue xv)
    {
        boolean x = checkDataRowExists("Routes", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "Routes", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "Routes");

        return rs;
    }

    public synchronized long addOrUpdateRouteBus(RouteAssignValue xv)
    {
        boolean x = checkDataRowExists("Route_Bus", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "Route_Bus", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "Route_Bus");

        return rs;
    }

    public synchronized long addOrUpdateStage(StageValue xv)
    {
        boolean x = checkDataRowExists("Stage", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "Stage", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "Stage");

        return rs;
    }

    public synchronized long addOrUpdateBusStation(BusStationValue xv)
    {
        boolean x = checkDataRowExists("BusStation", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "BusStation", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "BusStation");

        return rs;
    }

    public synchronized long addOrUpdateBusStop(BusStopValue xv)
    {
        boolean x = checkDataRowExists("BusStop", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "BusStop", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "BusStop");

        return rs;
    }

    /*public List<BusValue> getBusList()
    {
        String sql = String.format("select * from Bus");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor == null)
            return null;

        List<BusValue> list = new ArrayList<BusValue>();
        if (cursor.moveToFirst())
        {
            do
            {
                BusValue xx = new BusValue();
                xx.BusNumber = cursor.getString(cursor.getColumnIndex("busnumber"));
                xx.Capacity = cursor.getInt(cursor.getColumnIndex("capacity"));
                xx.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
                //--
                list.add(xx);
            }
            while (cursor.moveToNext());
        }

        cursor.close();
        db.close();

        return list;
    }*/

    public BusValue getBusByISN(int busISN)
    {
		BusValue xx = null;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = "select * from Bus where isn=?";
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(busISN)});

				if (cursor == null)
					return null;

				if (cursor.moveToFirst())
				{
					xx = new BusValue();
					xx.BusNumber = cursor.getString(cursor.getColumnIndex("busnumber"));
					xx.Capacity = cursor.getInt(cursor.getColumnIndex("capacity"));
					xx.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
				}

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getBusByISN] Exception: " + ex.toString());
			}
		}//end of synchronized

        return xx;
    }

    public List<RouteValue> getShortHaulRoutesOfBus(int busISN)
    {
		List<RouteValue> list = null;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = String.format("select x.* from Routes x inner join Route_Bus y " +
											"on x.ISN = y.RouteISN where y.BusISN=%d and x.Type = 2", busISN);
				//Log.d(TAG, "[getShortHaulRoutesOfBus] " + sql);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, null);

				if (cursor == null)
					return null;

				list = new ArrayList<RouteValue>();
				if (cursor.moveToFirst())
				{
					do
					{
						RouteValue xx = new RouteValue();
						xx.Name = cursor.getString(cursor.getColumnIndex("name"));
						xx.Type = cursor.getInt(cursor.getColumnIndex("type"));
						xx.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
						//--
						list.add(xx);
					}
					while (cursor.moveToNext());
				}

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getShortHaulRoutesOfBus] Exception: " + ex.toString());
			}
		}//end of synchronized

        return list;
    }

    /*public List<ConductorValue> getDriverList()
    {
        String sql = String.format("select isn, username, fullname from Conductors");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor == null)
            return null;

        List<ConductorValue> list = new ArrayList<ConductorValue>();
        if (cursor.moveToFirst())
        {
            do
            {
                ConductorValue xx = new ConductorValue();
                xx.UserName = cursor.getString(cursor.getColumnIndex("username"));
                xx.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
                xx.FullName = cursor.getString(cursor.getColumnIndex("fullname"));
                //--
                list.add(xx);
            }
            while (cursor.moveToNext());
        }

        cursor.close();
        db.close();

        return list;
    }*/

    public List<ConductorValue> getDriverOfBus(int busISN)
    {
		List<ConductorValue> list = null;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = String.format("select c.isn, c.username, c.fullname from Conductors c \n" +
						"\tinner join BusAssignment b on c.isn = b.staffisn\n" +
						"where b.busisn = ?");
				//Log.d(TAG, "[getDriverOfBus] " + sql);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(busISN)});

				if (cursor == null)
					return null;

				list = new ArrayList<ConductorValue>();
				if (cursor.moveToFirst())
				{
					do
					{
						ConductorValue xx = new ConductorValue();
						xx.UserName = cursor.getString(cursor.getColumnIndex("username"));
						xx.ISN = cursor.getInt(cursor.getColumnIndex("isn"));
						xx.FullName = cursor.getString(cursor.getColumnIndex("fullname"));
						//--
						list.add(xx);
					}
					while (cursor.moveToNext());
				}

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getDriverOfBus] Exception: " + ex.toString());
			}
		}//end of synchronized

        return list;
    }

    public synchronized List<StagesOfRouteValue> getStagesOfRoute(int routeISN)
    {
        return getStagesOfRouteAfter(routeISN, 0);
    }

    public List<StagesOfRouteValue> getStagesOfRouteAfter(int routeISN, int stageOrder)
    {
		List<StagesOfRouteValue> list = null;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = String.format("select * from vw_stage\n" +
						"where routeisn=%d and stageorder > %d", routeISN, stageOrder);
				//---
				//Log.d(TAG, "[getStagesOfRouteAfter] " + sql);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, null);

				if (cursor == null)
					return null;

				list = new ArrayList<StagesOfRouteValue>();
				if (cursor.moveToFirst())
				{
					do
					{
						StagesOfRouteValue xx = new StagesOfRouteValue();
						for(Field field: xx.getClass().getFields())
						{
							try
							{
								if(cursor.getColumnIndex(field.getName().toLowerCase()) == -1) //not found
									continue;

								Type type = field.getGenericType();
								if(type instanceof ParameterizedType)
									continue; //not process yet
								//--
								type = field.getType();

								if(type.equals(String.class))
									field.set(xx, cursor.getString(cursor.getColumnIndex(field.getName().toLowerCase())));
								else if(type.equals(Long.TYPE))
									field.set(xx, cursor.getLong(cursor.getColumnIndex(field.getName().toLowerCase())));
								else if(type.equals(Date.class))
									field.set(xx, Utilities.StringToDate(cursor.getString(
											cursor.getColumnIndex(field.getName().toLowerCase()))));
								else if(type.equals(Integer.TYPE))
									field.set(xx, cursor.getInt(cursor.getColumnIndex(field.getName().toLowerCase())));
								else if(type.equals(Double.TYPE))
									field.set(xx, cursor.getDouble(cursor.getColumnIndex(field.getName().toLowerCase())));
							}
							catch (IllegalAccessException e)
							{
								e.printStackTrace();
								Log.e(TAG, "[getStagesOfRouteAfter] IllegalAccessException: " + e.toString());
							}
						}
						//--
						list.add(xx);
					}
					while (cursor.moveToNext());
				}
				else
				{
					Log.d(TAG, "[getStagesOfRouteAfter] No data found");
				}

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getStagesOfRouteAfter] Exception: " + ex.toString());
			}
		}//end of synchronized

        return list;
    }

    // add journal tracking
    public synchronized long addJournal(JournalValue value)
    {
        long rs = addTableRowWithExclusive(value, "Journal", Arrays.asList("ISN"));
        return rs;
    }

    public synchronized long updateJournal(JournalValue value)
    {
        long rs = updateTableRow(value, "Journal", "ISN", value.ISN);
        return rs;
    }

    public synchronized long addOrUpdateExpressFare(ExpressFareValue xv)
    {
        boolean x = checkDataRowExists("ExpressFare", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "ExpressFare", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "ExpressFare");

        return rs;
    }

    public synchronized long addOrUpdateStageFare(StageFareValue xv)
    {
        boolean x = checkDataRowExists("StageFare", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "StageFare", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "StageFare");

        return rs;
    }

    public synchronized long addOrUpdateBusSeat(BusSeatValue xv)
    {
        boolean x = checkDataRowExists("BusSeat", "ISN", xv.ISN);
        long rs;
        if (x)
            rs = updateTableRow(xv, "BusSeat", "ISN", xv.ISN);
        else
            rs = addTableRow(xv, "BusSeat");

        return rs;
    }

    /*public double getExpressFare(int routeISN, int stage1ISN, int stage2ISN)
    {
        String sql = String.format("select * from expressfare " +
                "where routeisn=%d and stage1isn=%d and stage2isn=%d",
                routeISN, stage1ISN, stage2ISN);
        //---
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor == null)
            return -1;

        if (cursor.moveToFirst())
        {
            double fare = cursor.getDouble(cursor.getColumnIndex("fare"));
            return fare;
        }
        cursor.close();
        db.close();

        return -2;
    }*/

    public double getStageFare(int routeISN, int xStage)
    {
		double fare = 0;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = String.format("select * from StageFare " +
						"where routeisn=%d and xStage=%d order by isn desc;",
						routeISN, xStage);
				//---
				//Log.d(TAG, "[getStageFare] " + sql);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, null);

				if (cursor == null)
					return -1;

				if (cursor.moveToFirst())
				{
					fare = cursor.getDouble(cursor.getColumnIndex("fare"));
				}
				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getStageFare] Exception: " + ex.toString());
			}
		}//end of synchronized

        return fare;
    }

    /*public List<BusSeatValue> getSeatOfBus(int busISN)
    {
        String sql = String.format("select * from BusSeat where BusISN=%d order by seatseq", busISN);
        //---
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);

        if (cursor == null)
            return null;

        List<BusSeatValue> list = new ArrayList<BusSeatValue>();
        if (cursor.moveToFirst())
        {
            do
            {
                BusSeatValue xx = new BusSeatValue();
                for(Field field: xx.getClass().getFields())
                {
                    try
                    {
                        if(cursor.getColumnIndex(field.getName().toLowerCase()) == -1) //not found
                            continue;

                        Type type = field.getGenericType();
                        if(type instanceof ParameterizedType)
                            continue; //not process yet
                        //--
                        type = field.getType();

                        if(type.equals(String.class))
                            field.set(xx, cursor.getString(cursor.getColumnIndex(field.getName().toLowerCase())));
                        else if(type.equals(Long.TYPE))
                            field.set(xx, cursor.getLong(cursor.getColumnIndex(field.getName().toLowerCase())));
                        else if(type.equals(Date.class))
                            field.set(xx, Utilities.StringToDate(cursor.getString(
                                    cursor.getColumnIndex(field.getName().toLowerCase()))));
                        else if(type.equals(Integer.TYPE))
                            field.set(xx, cursor.getInt(cursor.getColumnIndex(field.getName().toLowerCase())));
                        else if(type.equals(Double.TYPE))
                            field.set(xx, cursor.getDouble(cursor.getColumnIndex(field.getName().toLowerCase())));
                    }
                    catch (IllegalAccessException e)
                    {
                        e.printStackTrace();
                    }
                }
                //--
                list.add(xx);
            }
            while (cursor.moveToNext());
        }

        cursor.close();
        db.close();

        return list;
    }*/

    public synchronized long addTransaction(TransactionValue xx)
    {
		Log.i(TAG, "[addTransaction] 1. Add transaction...");
        long rs = addTableRowWithExclusive(xx, "TransactionInfo",
                Arrays.asList("ISN", "Passengers", "VoucherDiscount"));

        Log.i(TAG, "[addTransaction] result: " + rs);

        if(rs <= 0)
            return rs;

        if(xx.VoucherDiscount != null)
        {
			Log.i(TAG, "[addTransaction] 2. Update voucher discount...");
            updateTableRow(xx.VoucherDiscount, "TransactionInfo", "ISN", rs);
        }
        if(xx.Passengers != null && xx.Passengers.size() > 0)
        {
			Log.i(TAG, "[addTransaction] 3. Add transaction detail...");
            for(CheckInPassengerValue cp : xx.Passengers)
            {
                long k = addTableRow(
                    //xx,
                    new TransactionDetailValue((int)rs, cp.Type, cp.SeatNumber, cp.Fare),
                    "TransactionDetail"
                );
                Log.i(TAG, "[addTransaction] result: " + k);
            }
        }

        return rs;
    }

    //TransactionInfo
    public synchronized TransactionValue getLastCheckedOutTransaction(int memberISN)
    {
        String sql = String.format("select * from TransactionInfo \n" +
                "where paymenttype = 'CHECK-IN' and type in('BMF', 'Cash', 'Cash-TopUp') \n" +
                "and checkoutdate is not null and memberisn = ? \n" +
                "order by isn desc");

        List<TransactionValue> lastestTrans = getTableRows(
                sql,
                new String[]{String.valueOf(memberISN)},
                TransactionValue.class);

        if(lastestTrans != null && lastestTrans.size() > 0)
            return lastestTrans.get(0);

        return null;
    }

    public synchronized TransactionValue getCheckedOutTransactionByID(String transactionID)
    {
        String sql = String.format("select * from TransactionInfo \n" +
                "where paymenttype = 'CHECK-IN' and type in('BMF', 'Cash', 'Cash-TopUp')\n" +
                "and checkoutdate is not null and transactionid=?\n" +
                "order by isn desc");

        List<TransactionValue> lastestTrans = getTableRows(
                sql,
                new String[]{String.valueOf(transactionID)},
                TransactionValue.class);

        if(lastestTrans != null && lastestTrans.size() > 0)
            return lastestTrans.get(0);

        return null;
    }

    public <T> List<T> getTableRows(String query, String[] paramValues, Class<T> cls)
    {
		List<T> result = null;
		synchronized (synchronizedLock)
		{
			try
			{
				//Log.d(TAG, "[getTableRows] " + query);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(query, paramValues);

				if (cursor == null)
					return null;

				if (cursor.moveToFirst())
				{
					result = new ArrayList<T>();
					do
					{
						try
						{
							T xx = cls.newInstance();
							for(Field field : xx.getClass().getFields())
							{
								try
								{
									if(cursor.getColumnIndex(field.getName().toLowerCase()) == -1) //not found
										continue;
									//--
									Type type = field.getGenericType();
									if(type instanceof ParameterizedType)
										continue; //not process yet
									//--
									type = field.getType();

									if(cursor.isNull(cursor.getColumnIndex(field.getName().toLowerCase())))
										continue;

									if(type.equals(String.class))
										field.set(xx, cursor.getString(cursor.getColumnIndex(field.getName().toLowerCase())));
									else if(type.equals(Long.TYPE))
										field.set(xx, cursor.getLong(cursor.getColumnIndex(field.getName().toLowerCase())));
									else if(type.equals(Date.class))
									{
										field.set(xx, Utilities.StringToDate(
												cursor.getString(
														cursor.getColumnIndex(field.getName().toLowerCase()))
										));
									}
									else if(type.equals(Integer.TYPE))
										field.set(xx, cursor.getInt(cursor.getColumnIndex(field.getName().toLowerCase())));
									else if(type.equals(Double.TYPE))
										field.set(xx, cursor.getDouble(cursor.getColumnIndex(field.getName().toLowerCase())));
								}
								catch (Exception ex)
								{
									Log.e(TAG, String.format("[getTableRows] field: %s. %s",
											field.getName(), ex.getMessage()));
								}
							}
							result.add(xx);
						}
						catch (Exception ex)
						{
							Log.e(TAG, "[getTableRows] " + ex.getMessage());
						}
					}while (cursor.moveToNext());
				}
				else
				{
					Log.d(TAG, "[getTableRows] No data found");
				}

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getTableRows] EXCEPTION: " + ex.getMessage());
			}
		}//end of synchronized

        return result;
    }

    public synchronized long addOrUpdateSyncStatus(SyncStatusValue value)
    {
        if(value == null)
            return -1;
        boolean x = checkDataRowExists("SyncStatus", "ISN", value.ISN);
        long rs;
        if (x)
            rs = updateTableRow(value, "SyncStatus", "ISN", value.ISN);
        else
            rs = addTableRow(value, "SyncStatus");

        return rs;
    }

    public synchronized SyncStatusValue getLastSync()
    {
        String sql = "select * from SyncStatus order by ISN desc limit 1;";
        List<SyncStatusValue> values = getTableRows(sql, null, SyncStatusValue.class);
        if(values != null && values.size() > 0)
            return values.get(0);
        return null;
    }

    public synchronized long addOrUpdateRuntimeValues(String name, String value)
    {
        if(name == null)
            return -1;
        long rs = 0;
        try
        {
            boolean x = checkDataRowExists("RuntimeValues", "name", name);

            if (x)
            {
                ContentValues xx = new ContentValues();
                xx.put("value", value);
                rs = updateTableRow(xx, "RuntimeValues", "name", name);
            }
            else
            {
                ContentValues xx = new ContentValues();
                xx.put("name", name);
                xx.put("value", value);
                rs = addTableRowByContentValues(xx, "RuntimeValues");
            }
        }
        catch (Exception ex)
        {
            Log.e(TAG, "[addOrUpdateRuntimeValues] EXCEPTION: " + ex.getMessage());
        }

        return rs;
    }

    public String getRuntimeValues(String name)
    {
		String result = "";
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = "select * from RuntimeValues where name=?";
				//Log.d(TAG, "[getRuntimeValues] " + sql);
				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, new String[]{name});

				if (cursor == null)
					return null;

				if (cursor.moveToFirst())
				{
					result = cursor.getString(cursor.getColumnIndex("value"));
				}
				else
				{
					Log.d(TAG, "[getRuntimeValues] No data found");
				}
				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getRuntimeValues] Exception: " + ex.toString());
			}
		}//end of synchronized

        return result;
    }

    public synchronized List<TransactionValue> getUnsyncTransaction()
    {
		Log.d(TAG, "[getUnsyncTransaction] Get transaction...");
        List<TransactionValue> unsyncTransaction = getTableRows(
                "select * from transactioninfo \n" +
                "where (paymenttype = 'CHECK-IN' and checkoutdate is not null and syncisn = 0) or \n" +
                "\t(paymenttype <> 'CHECK-IN' and syncisn = 0 ) ",
                null,
                TransactionValue.class);

        if(unsyncTransaction == null || unsyncTransaction.size() == 0)
            return null;
        for(TransactionValue xx : unsyncTransaction)
        {
			Log.d(TAG, "[getUnsyncTransaction] Get voucher info of transaction: " + xx.ISN);
            List<VoucherValue> vv = getTableRows(
                    "select vouchertype, voucheramount from transactioninfo where isn = ?",
                    new String[]{String.valueOf(xx.ISN)},
                    VoucherValue.class
            );
            if(vv != null && vv.size() > 0)
                xx.VoucherDiscount = vv.get(0);
            //---
			Log.d(TAG, "[getUnsyncTransaction] Get detail of transaction: " + xx.ISN);
            List<TransactionDetailValue> xtd = getTableRows(
                    "select * from transactiondetail where transactionisn = ?",
                    new String[]{String.valueOf(xx.ISN)},
                    TransactionDetailValue.class
            );
            List<CheckInPassengerValue> xpg = null;
            if(xtd != null && xtd.size() > 0)
            {
                xpg = new ArrayList<CheckInPassengerValue>();
                for(TransactionDetailValue yy: xtd)
                {
                    CheckInPassengerValue cv = new CheckInPassengerValue();
                    cv.SeatNumber = yy.SeatNumber;
                    cv.Fare = yy.Fare;
                    cv.Type = yy.PassengerType;

                    xpg.add(cv);
                }
            }

            xx.Passengers = xpg;
			try{Thread.sleep(50);}catch (Exception ex){}
        }

        return unsyncTransaction;
    }

    public synchronized int updateTransactionSyncResult(int transactionISN, int syncISN)
    {
        ContentValues param = new ContentValues();
        param.put("syncisn", syncISN);
        return updateTableRow(param, "TransactionInfo", "isn", transactionISN);
    }

    public synchronized long addOrUpdateBusAssign(BusAssignValue value)
    {
        if(value == null)
            return -1;
        boolean x = checkDataRowExists("BusAssignment", "ISN", value.ISN);
        long rs;
        if (x)
            rs = updateTableRow(value, "BusAssignment", "ISN", value.ISN);
        else
            rs = addTableRow(value, "BusAssignment");

        return rs;
    }

    public synchronized List<BusAssignValue> getBusesForStaff(int staffISN)
    {
        List<BusAssignValue> buses = getTableRows(
                "select * from BusAssignment where staffisn = ? order by isn desc",
                new String[]{String.valueOf(staffISN)},
                BusAssignValue.class);

        return buses;
    }

    public synchronized JournalValue getNewestJournal()
    {
        List<JournalValue> journals = getTableRows(
                "select * from Journal order by isn desc limit 1;",
                null,
                JournalValue.class
        );

        if(journals == null || journals.size() == 0)
            return null;
        return journals.get(0);
    }

    public synchronized List<JournalValue> getUnsyncJournal()
    {
        List<JournalValue> unsyncJournal = getTableRows(
                "select * from journal where syncisn = -1  "
                + "and (longitude <> 0 or latitude <> 0) and closestbusstation <> ''",
                null,
                JournalValue.class);

        if(unsyncJournal == null || unsyncJournal.size() == 0)
            return null;

        return unsyncJournal;
    }

    public synchronized int updateJournalSyncResult(int journalISN, int syncISN)
    {
        ContentValues param = new ContentValues();
        param.put("syncisn", syncISN);
        return updateTableRow(param, "journal", "isn", journalISN);
    }

    public synchronized long addOrUpdatePassengerType(PassengerTypeValue value)
    {
        if(value == null)
            return -1;
        boolean x = checkDataRowExists("PassengerType", "ISN", value.ISN);
        long rs;
        /*ArrayList<String> exclusive = new ArrayList<String>();
        exclusive.add("ADULT_PASSENGER");
        exclusive.add("CHILD_PASSENGER");
        exclusive.add("SENIOR_PASSENGER");
        exclusive.add("DISABLED_PASSENGER");*/
        if (x)
            rs = updateTableRow(value, "PassengerType", "ISN", value.ISN);
        else
            rs = addTableRow(value, "PassengerType");

        return rs;
    }

    public synchronized List<PassengerTypeValue> getPassengerType()
    {
        List<PassengerTypeValue> xx = getTableRows(
                "select * from PassengerType order by isn desc",
                null,
                PassengerTypeValue.class);

        return xx;
    }

    public int getStageCountOfRoute(int routeISN)
    {
		int count = 0;
		synchronized (synchronizedLock)
		{
			try
			{
				String sql = "select distinct routeisn, stageisn from vw_stage where routeisn=?";

				SQLiteDatabase db = this.getReadableDatabase();
				Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(routeISN)});

				if (cursor == null)
					return 0;

				count = cursor.getCount();

				cursor.close();
				db.close();
			}
			catch (Exception ex)
			{
				Log.e(TAG, "[getStageCountOfRoute] Exception: " + ex.toString());
			}
		}//end of synchronized

        return count;
    }

    public synchronized long setTransactionIsRefunded(int transactionISN)
    {
        ContentValues param = new ContentValues();
        param.put("isrefunded", 1);
        return updateTableRow(param, "TransactionInfo", "isn", transactionISN);
    }

    public synchronized TransactionValue getLastCheckInTransaction(int memberISN, int routeISN, int busISN)
    {
		Log.d(TAG, "[getLastCheckInTransaction] Get transaction...");
        String sql = "select * from transactioninfo \n" +
                "where paymenttype = 'CHECK-IN' and checkoutdate is null and \n" +
                "\tmemberisn=? and routeisn=? and busisn=?\n" +
                "order by isn desc limit 1";
        List<TransactionValue> result = getTableRows(
                sql,
                new String[]{String.valueOf(memberISN), String.valueOf(routeISN), String.valueOf(busISN)},
                TransactionValue.class
        );

        if(result == null || result.size() == 0)
            return null;

        TransactionValue xx = result.get(0);
        //--
		Log.d(TAG, "[getLastCheckInTransaction] Get voucher...");
        List<VoucherValue> vv = getTableRows(
                "select vouchertype, voucheramount from transactioninfo where isn = ?",
                new String[]{String.valueOf(xx.ISN)},
                VoucherValue.class
        );
        if(vv != null && vv.size() > 0)
            xx.VoucherDiscount = vv.get(0);
        //---
		Log.d(TAG, "[getLastCheckInTransaction] Get transaction detail...");
        List<TransactionDetailValue> xtd = getTableRows(
                "select * from transactiondetail where transactionisn = ?",
                new String[]{String.valueOf(xx.ISN)},
                TransactionDetailValue.class
        );
        List<CheckInPassengerValue> xpg = null;
        if(xtd != null && xtd.size() > 0)
        {
            xpg = new ArrayList<CheckInPassengerValue>();
            for(TransactionDetailValue yy: xtd)
            {
                CheckInPassengerValue cv = new CheckInPassengerValue();
                cv.SeatNumber = yy.SeatNumber;
                cv.Fare = yy.Fare;
                cv.Type = yy.PassengerType;

                xpg.add(cv);
            }
        }

        xx.Passengers = xpg;

        return xx;
    }

    public synchronized int updateTransactionCheckOut(TransactionValue xx)
    {
        if(xx == null)
            return -1;

        return updateTableRow(xx, "TransactionInfo", "isn", xx.ISN);
    }

    public synchronized StagesOfRouteValue getFirstStationOfStageOrder(int routeISN, int stageISN, int stageOrder)
    {
        //select * from vw_stage where routeisn=? and stageisn=? and  stageorder=? limit 1
        List<StagesOfRouteValue> stations = getTableRows(
                "select * from vw_stage where routeisn=? and stageisn=? and  stageorder=? limit 1",
                new String[]{
                        String.valueOf(routeISN),
                        String.valueOf(stageISN),
                        String.valueOf(stageOrder)
                },
                StagesOfRouteValue.class);
        if(stations != null && stations.size() > 0)
            return stations.get(0);
        return null;
    }

    public synchronized StagesOfRouteValue getStageWithStation(int routeISN, int stageISN, int stationISN)
    {
        //select * from vw_stage where routeisn=? and stageisn=? and  busstationisn=?
        List<StagesOfRouteValue> stages = getTableRows(
                "select * from vw_stage where routeisn=? and stageisn=? and  busstationisn=?",
                new String[]{
                        String.valueOf(routeISN),
                        String.valueOf(stageISN),
                        String.valueOf(stationISN)
                },
                StagesOfRouteValue.class);
        if(stages != null && stages.size() > 0)
            return stages.get(0);
        return null;
    }

    public int deleteData(String tableName, String criteria)
    {
        int x = -1;
		synchronized (synchronizedLock)
		{
			try
			{
				Log.d(TAG, String.format("[deleteData] Table: %s where %s", tableName, criteria));
				SQLiteDatabase db = this.getWritableDatabase();
				x = db.delete(tableName, criteria, null);
				db.close();
			}
			catch (Exception ex)
			{
				Log.d(TAG, "[deleteData] EXCEPTION: " + ex);
			}
		}//end of synchronized

        return x;
    }

	public synchronized ConductorValue getConductorByISN(int isn)
	{
		List<ConductorValue> xx = getTableRows("select * from conductors where isn=?",
				new String[]{String.valueOf(isn)},
				ConductorValue.class);
		if(xx != null && xx.size() > 0)
			return xx.get(0);
		return null;
	}

	public synchronized RouteValue getRouteByISN(int isn)
	{
		List<RouteValue> xx = getTableRows("select * from routes where isn=?",
				new String[]{String.valueOf(isn)},
				RouteValue.class);
		if(xx != null && xx.size() > 0)
			return xx.get(0);
		return null;
	}

	public synchronized JournalValue getJournalByISN(int isn)
	{
		List<JournalValue> xx = getTableRows("select * from journal where isn=?",
				new String[]{String.valueOf(isn)},
				JournalValue.class);
		if(xx != null && xx.size() > 0)
			return xx.get(0);
		return null;
	}
}
